package com.roc.web.action.terminal;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class TermianlActForYsbSearchCondition extends BaseSearchCondition{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	private String areaName; //大区名称
	private String posType; //终端类型	0定制机 2携机入网
	
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		if(Constance.isNotEmpty(areaName)){	//大区
			filedBeanList.add(new WhereHQLFieldBean("rr.areaName", areaName.trim(), "=", false));
		}
		if(Constance.isNotEmpty(posType) && !"99".equals(posType)){	//大区
			filedBeanList.add(new WhereHQLFieldBean("rr.posType", posType.trim(), "=", false));
		}
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer sb = new StringBuffer();
		sb.append("select count(1) from ");
		sb.append("(select dd.name areaName,dd.agent_id oneagentName,dd.account_no oneagentNo,");
		sb.append("count(device_cd) posCount,decode(dd.asset_flag, 2, '携机入网', '定制机') posType ");
		sb.append("from (select distinct i.device_cd,f.asset_flag,(select s.real_name from apsmgm.t_operators s ");
		sb.append("where s.id = a.agent_id) agent_id,(select (select e.name from apsmgm.tbl_area e where e.id = d.companyid) ");
		sb.append("from apsmgm.t_operators d where d.id = a.agent_id) name,ss.account_no ");
		sb.append("from apsmgm.tbl_mcht_inf t,apsmgm.tbl_term_inf i,apsmgm.t_pos_info f,apsmgm.t_pos_agent a,");
		sb.append("(select o.id, o.account_no from apsmgm.t_operators o where ((o.role_type = '02' and o.supri_id <> '40001') or ");
		sb.append("(o.role_type = '03' and o.supri_id in (select s.id from apsmgm.t_operators s where s.role_type in ('02', '03') and s.supri_id = '40001'))) ");
		sb.append("and o.real_name not like '%测试%' and o.companyid in (select id from apsmgm.tbl_area)) ss ");
		sb.append("where t.group_cd = '3' and t.mcht_cd = i.mcht_cd and i.device_cd = f.serial_num and a.agent_id = ss.id ");
		sb.append("and a.serial_num = i.device_cd and f.asset_flag in ('2', '0')) dd group by dd.agent_id,dd.name,dd.asset_flag,dd.account_no) rr where 1=1 ");
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb = new StringBuffer();
		sb.append("select areaName,oneagentName,oneagentNo,posCount,posType,yesdsy from ");
		sb.append("(select dd.name areaName,dd.agent_id oneagentName,dd.account_no oneagentNo,sum(yesdsy) yesdsy, ");
		sb.append("count(device_cd) posCount,decode(dd.asset_flag, 2, '携机入网', '定制机') posType ");
		sb.append("from (select distinct i.device_cd,f.asset_flag,(select s.real_name from apsmgm.t_operators s ");
		sb.append("where s.id = a.agent_id) agent_id,(select (select e.name from apsmgm.tbl_area e where e.id = d.companyid) ");
		sb.append("from apsmgm.t_operators d where d.id = a.agent_id) name,  decode(to_char(i.rec_upd_ts,'yyyyMMdd'),to_char(sysdate-1,'yyyyMMdd'),1,0) yesdsy,   ss.account_no ");
		sb.append("from apsmgm.tbl_mcht_inf t,apsmgm.tbl_term_inf i,apsmgm.t_pos_info f,apsmgm.t_pos_agent a,");
		sb.append("(select o.id, o.account_no from apsmgm.t_operators o where ((o.role_type = '02' and o.supri_id <> '40001') or ");
		sb.append("(o.role_type = '03' and o.supri_id in (select s.id from apsmgm.t_operators s where s.role_type in ('02', '03') and s.supri_id = '40001'))) ");
		sb.append("and o.real_name not like '%测试%' and o.companyid in (select id from apsmgm.tbl_area)) ss ");
		sb.append("where t.group_cd = '3' and t.mcht_cd = i.mcht_cd and i.device_cd = f.serial_num and a.agent_id = ss.id ");
		sb.append("and a.serial_num = i.device_cd and f.asset_flag in ('2', '0')) dd group by dd.agent_id,dd.name,dd.asset_flag,dd.account_no) rr where 1=1 ");
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		return " order by rr.areaName,rr.oneagentName,rr.oneagentNo ";
	}

	public String getAreaName() {
		return areaName;
	}

	public void setAreaName(String areaName) {
		this.areaName = areaName;
	}

	public String getPosType() {
		return posType;
	}

	public void setPosType(String posType) {
		this.posType = posType;
	}

}
